using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Windows.Forms;
using FirebirdSql.Data.FirebirdClient;
using FirebirdSql.Data.Isql;

using Net.SamuelChen.Production.SmartAssistant;

namespace Net.SamuelChen.Libs.DataAccess {
    /// <summary>
    /// Summary description for CreateDatabase.
    /// </summary>
    public class Database {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(Database));
        private const string format = "yyyy-MM-dd HH:mm:ss";

        private static DBMS _dbsystem = DBMS.OleDB;

        public Database() {
        }

        #region properties
        public static DBMS DBSystem {
            get {return _dbsystem;}
        }
        #endregion

        private static IDbConnection CreateConnection() {
            
            IDbConnection conn = null;
            switch (Configuration.DBSystem) {
                case DBMS.OleDB:
                    conn = new System.Data.OleDb.OleDbConnection();
                    break;
                case DBMS.FireBird:
                    conn = new FbConnection();
                    break;
                case DBMS.MSSql:
                    conn = new System.Data.SqlClient.SqlConnection();
                    break;
                case DBMS.XML:
                    break;
                default:
                    break;
            }
            return conn;
        }

        private static IDbConnection CreateConnection(string connectionString) {
            IDbConnection conn = CreateConnection();
            conn.ConnectionString = connectionString;
            return conn;
        }

        //		private static IDbCommand CreateCommand() {
        //			IDbCommand cmd = null;
        //			switch (Configuration.DBSystem) {
        //				case DBMS.FireBird:
        //					cmd = new FbCommand();
        //					break;
        //				case DBMS.MSSQL:
        //					cmd = new System.Data.SqlClient.SqlCommand();
        //					break;
        //				case DBMS.XML:
        //					break;
        //				default:
        //					break;
        //			}
        //			return cmd;			
        //		}
        //		private static IDbCommand CreateCommand(IDbConnection connection) {
        //			IDbCommand cmd = CreateCommand();
        //			cmd.Connection = connection;
        //			return cmd;
        //		}
        //		private static IDbCommand CreateCommand(string text) {
        //			IDbCommand cmd = CreateCommand();
        //			cmd.CommandText = text;
        //			return cmd;
        //		}
        //		private static IDbCommand CreateCommand(IDbConnection connection, string text) {
        //			IDbCommand cmd = CreateCommand();
        //			cmd.Connection = connection;
        //			cmd.CommandText = text;
        //			return cmd;
        //		}

        private static IDataParameter CreateParameter(string name, object val) {
            IDataParameter parm = null;
            switch (Configuration.DBSystem) {
                case DBMS.OleDB:
                    parm = new System.Data.OleDb.OleDbParameter(name, val);
                    break;               
                case DBMS.FireBird:
                    parm = new FbParameter(name, val);
                    break;
                case DBMS.MSSql:
                    parm = new System.Data.SqlClient.SqlParameter(name, val);
                    break;
                case DBMS.XML:
                    break;
                default:
                    break;
            }
            return parm;
        }

        /*
         * Create New Database with given script
         * return connection string
         */

        public static bool CreateNew() {
            bool ret = false;
            switch (Configuration.DBSystem) {
                case DBMS.OleDB:
                    ret = CreateNewOleDB();
                    break;
                case DBMS.FireBird:
                    ret = CreateNewFireBird();
                    break;
                case DBMS.MSSql:
                    ret = CreateNewMSSql();
                    break;
                case DBMS.XML:
                    break;
                default:
                    break;
            }
            return ret;
        }

        public static bool CreateNewOleDB() {

            return true;
        }

        public static bool CreateNewFireBird() {

            string pathDb = Configuration.Database;
            string pathScript = Configuration.DBScriptPath + @"db_firebird.sql";

            // construct the connection string
            FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
            csb.ServerType = FbServerType.Embedded; // we are using the embedded Firebird
            csb.Database = pathDb;
            csb.Password = Configuration.DBUser;
            csb.UserID = Configuration.DBPassword;

            if (File.Exists(csb.Database)) {
                //throw new ApplicationException("The database file does already exist.");
                DialogResult rt = MessageBox.Show("The database file does already exist. \nDo you want to delete it?"
                    , "Create Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
                if (rt == DialogResult.Yes)
                    File.Delete(csb.Database);
                else
                    return false;
            }

            // create a new database
            FbConnection.CreateDatabase(csb.ToString());

            // parse the SQL script
            FbScript script = new FbScript(pathScript);
            script.Parse();

            // execute the SQL script$
            using (FbConnection c = new FbConnection(csb.ToString())) {
                FbBatchExecution fbe = new FbBatchExecution(c);
                foreach (string cmd in script.Results) {
                    fbe.SqlStatements.Add(cmd);
                }
                fbe.Execute();
            }

            return true;
        }

        public static bool CreateNewMSSql() {
            /**
             * We could use System.Data.SqlServerCe.SqlCeEngine here
             */

            MessageBox.Show("MSSql is not supported here.", "Google Helper",
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            return false;
            //throw new ApplicationException("MSSql is not supported here by now.");
            //			return;
            //			string pathScript = Configuration.DBScriptPath + @"db_mssql.sql";
            //
            //			IDbConnection conn = new System.Data.SqlClient.SqlConnection();
            //			conn.ConnectionString = Configuration.DBConnectionString;
            //
            //			IDbCommand cmd = conn.CreateCommand();
            //			cmd.CommandType = CommandType.Text;
            //			cmd.CommandText = "CREATE DATABASE CALENDAR";
            //
            //			StreamReader sr = new StreamReader(pathScript);
            //			cmd.CommandText = sr.ReadToEnd();
            //			cmd.ExecuteNonQuery();
        }

        public static bool[] GetScheduleDaysOfMonth(int year, int month) {
            bool[] days = new bool[DateTime.DaysInMonth(year, month) + 1];

            DateTime begin = new DateTime(year, month, 1, 0, 0, 0);
            DateTime end = new DateTime(year, month, 30, 23, 59, 59);

            using (IDbConnection conn = CreateConnection(Configuration.DBConnectionString)) {

                string sqlQuery = "SELECT * FROM SCHEDULE WHERE (DATE_START BETWEEN @start AND @stop)" +
                    " OR (DATE_STOP BETWEEN @start AND @stop) ORDER BY DATE_STOP";

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(CreateParameter("@start", begin));
                cmd.Parameters.Add(CreateParameter("@stop", end));

                DateTime dt1, dt2;
                try {
                    conn.Open();
                    using (IDataReader reader = cmd.ExecuteReader()) {

                        int fldStart = reader.GetOrdinal("DATE_START");
                        int fldStop = reader.GetOrdinal("DATE_STOP");

                        while (reader.Read()) {
                            dt1 = reader.GetDateTime(fldStart);
                            dt2 = reader.GetDateTime(fldStop);
                            days.SetValue(true, dt1.Day);
                            days.SetValue(true, dt2.Day);
                        }
                    }
                } catch (DataException err) {
                    log.Error("Error occurred while loading schedule between " + begin.ToString()
                        + "and " + end.ToString() + ".", err);
                }

                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

            return days;
        }

        public static IList GetScheduleListBetween(DateTime begin, DateTime end) {
            IList schedule = new ArrayList(0);
            using (IDbConnection conn = CreateConnection(Configuration.DBConnectionString)) {

                string sqlQuery = "SELECT * FROM SCHEDULE WHERE (DATE_START BETWEEN @start AND @stop)" +
                    " OR (DATE_STOP BETWEEN @start AND @stop) ORDER BY DATE_STOP";

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(CreateParameter("@start", begin));
                cmd.Parameters.Add(CreateParameter("@stop", end));

                try {
                    conn.Open();
                    using (IDataReader reader = cmd.ExecuteReader()) {

                        int fldID = reader.GetOrdinal("ID");
                        int fldDesc = reader.GetOrdinal("DESCRIPTION");
                        int fldStart = reader.GetOrdinal("DATE_START");
                        int fldStop = reader.GetOrdinal("DATE_STOP");

                        while (reader.Read()) {
                            ScheduleItem item = new ScheduleItem();
                            item.ID = reader.GetInt32(fldID);
                            item.Description = reader.GetString(fldDesc);
                            item.DateStart = reader.GetDateTime(fldStart);
                            item.DateStop = reader.GetDateTime(fldStop);
                            schedule.Add(item);
                        }
                    }
                } catch (DataException err) {
                    log.Error("Error occurred while loading schedule between " + begin.ToString()
                        + "and " + end.ToString() + ".", err);
                }

                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

            return schedule;
        }

        //		public static DataSet GetScheduleSetBetween(DateTime begin, DateTime end) {
        //
        //			DataSet ds = new DataSet();
        //			string sqlQuery = "SELECT * FROM SCHEDULE WHERE (DATE_START <= '" + begin.ToString(format)
        //				+ "') AND (DATE_STOP >= '" + end.ToString(format) + "')";
        //			log.Debug("SQL: " + sqlQuery);
        //
        //			IDbDataAdapter adapter = new FbDataAdapter(sqlQuery, Configuration.DBConnectionString);
        //
        //			try{
        //				adapter.Fill(ds);
        //			} catch (DataException err) {
        //				log.Error("Error occurred while loading schedule between " + begin.ToString()
        //					+ "and " + end.ToString() + ".", err);
        //			}
        //
        //			return ds;
        //		}

        public static void InsertScheduleItem(string description, DateTime begin, DateTime end) {

            using (IDbConnection conn = CreateConnection(Configuration.DBConnectionString)) {

                string sqlQuery = "INSERT INTO SCHEDULE (ID, DESCRIPTION, DATE_START, DATE_STOP) VALUES (GEN_ID(GEN_SCHEDULE_ID, 1), @desc, @start, @stop)";

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(CreateParameter("@desc", description));
                cmd.Parameters.Add(CreateParameter("@start", begin));
                cmd.Parameters.Add(CreateParameter("@stop", end));

                try {
                    conn.Open();
                    cmd.ExecuteNonQuery();

                } catch (DataException err) {
                    log.Error("Error occurred while inserting schedule between " + begin.ToString()
                        + "and " + end.ToString() + ".", err);
                }

                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

        }

        public static void UpdateScheduleItem(int id, string description, DateTime begin, DateTime end) {

            using (IDbConnection conn = CreateConnection(Configuration.DBConnectionString)) {

                string sqlQuery = "UPDATE SCHEDULE SET DESCRIPTION=@desc, DATE_START=@start, DATE_STOP=@stop WHERE (ID=@id)";

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(CreateParameter("@desc", description));
                cmd.Parameters.Add(CreateParameter("@start", begin));
                cmd.Parameters.Add(CreateParameter("@stop", end));
                cmd.Parameters.Add(CreateParameter("@id", id));

                try {
                    conn.Open();
                    cmd.ExecuteNonQuery();

                } catch (DataException err) {
                    log.Error("Error occurred while updating schedule whose ID is " + id.ToString() + ".", err);
                }

                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

        }
    }
}
